/*
1、根据过去24小时的书，生成国控点天数据
pro_stat_guokong_data_day 国控点数据统计
call pro_stat_guokong_data_day('2021-10-09 23:00:00');
call pro_stat_guokong_data_day('2021-10-09 03:00:00');

select * from t_d_guokong_hour;
select * from t_d_guokong_day;

delete from t_d_guokong_day;

create table t_d_guokong_day as select * from t_d_guokong_hour where 1<>1;

*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `pro_stat_guokong_data_day`;
CREATE PROCEDURE `pro_stat_guokong_data_day`(in time_start varchar(40))
BEGIN

-- 1、找到每辆taxi 每分钟的最后位置，插入t_d_taxi_position表；  不做
-- 2、生成 gid_100 if gid_100 is null;
-- 3、判断是否满足小时统计条件，统计各类hour数据；
-- 4、判断是否满足天统计条件，统计各类day数据；

		declare PostTime_ datetime ;
		DECLARE hour_start_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);	
		DECLARE hour_end_ datetime DEFAULT now();			
		DECLARE day_start_ datetime DEFAULT now();	
		DECLARE day_end_ datetime DEFAULT now();				
		DECLARE has_data_ int DEFAULT 0;		
		DECLARE has_day_data_ int DEFAULT 0;			
		
		set hour_start_ = time_start;

		set hour_start_ = concat(date(hour_start_),' ',LPAD(HOUR(hour_start_),2,0),':00:00');
		set hour_end_ = DATE_ADD(hour_start_,INTERVAL 1 HOUR);
		set day_end_ = concat(date(hour_start_),' 00:00:00');
		set day_start_ = DATE_SUB(day_end_,INTERVAL 24 HOUR);
		
		select 1 into has_day_data_ from t_d_guokong_day where date(TimePoint)=date(day_start_) limit 1;

		#select hour_start_,hour_end_,day_start_,day_end_,has_data_,has_day_data_ ;

		# 每日3点  统计前一天的国控点数据
		IF has_day_data_=0 and hour(time_start)>=3 THEN
			insert into t_d_guokong_day (SiteID,TimePoint,Area,PositionName,Latitude,Longitude,
					PM25,PM10,CO,NO2,SO2,O31,O38,PValue,Quality) 
				select d.SiteID,day_start_,d.Area,d.PositionName,d.Latitude,d.Longitude,
					round(avg(PM25),0) as PM25,round(AVG(PM10),0) as PM10,round(avg(CO),2) as CO,round(avg(NO2),2) as NO2,
					round(avg(SO2),0) as SO2,round(avg(O31),0) as O31,round(avg(O38),0) as O38,
					round(avg(PValue),0) as PValue,
					case when round(avg(AQI),0)<=50  then '优' 
							when round(avg(AQI),0)<=100 and round(avg(AQI),0)>50 then '良' 
							when round(avg(AQI),0)<=150 and round(avg(AQI),0)>100 then '轻度污染'
							when round(avg(AQI),0)<=200 and round(avg(AQI),0)>150 then '中度污染'
							when round(avg(AQI),0)<=300 and round(avg(AQI),0)>200 then '重度污染'
							when round(avg(AQI),0)>300 then '严重污染' end as Quality 
					from t_d_guokong_hour d 
					where date(TimePoint)=date(day_start_) #date(TimePoint)=date('2021-09-08 00:00:00') 
					group by d.SiteID,day_start_,d.Area,d.PositionName,d.Latitude,d.Longitude;

	
		END IF;			

			
END
//
DELIMITER ;